﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using GBG_DBM.Support.DAL;
using GBG_DBM.Support.DAL.Entities;
using System.Data.SqlClient;
using System.Data;


namespace GBG_DBM.Support.DAL.Entities
{
    internal class DAO
    {
       
            #region Property
            private CUR _CUR;
            private Packaging _Packaging;
            private Supplier_RM _Supplier;
            private RawMaterialSupplier _RawMaterialSupplier;
            private Freight _Freight;
            private STD _STD;

            #endregion
            internal STD getSTD()
            {

                _STD = new STD();
                string sql = "Select * from [STD QTY]";
                try
                {
                    DBHelper.GetDBConnection.Open();
                    OleDbDataReader Reader = DBHelper.GetDataReader(sql);
                    //Reader
                    while (Reader.Read())
                    {
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
                return _STD;
            }
			internal STD getSTDByCode(string Code) 
			{
				OleDbCommand cmd;
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				OleDbDataReader reader;
				STD result = null;
				try
				{
					connection.Open();
					cmd = new OleDbCommand("Select * from [STD QTY] where Product_ID='" + Code + "'", connection);
					reader = cmd.ExecuteReader();
					if (reader.Read())
					{
						result = new STD();
                        result.ProductId = Convert.ToString(reader["Product_ID"]);
                        result.ProductName = Convert.ToString(reader["Product_Name"]);
                        result.ProductIngredient = Convert.ToString(reader["Product_Ingredient"]);
                        result.ProductUnit = Convert.ToInt32(reader["Product_Unit"]);
                        result.Loss = Convert.ToSingle(reader["Loss"]);
                        result.BatchSize = Convert.ToString(reader["Batch_Size"]);
                        result.RRP = Convert.ToInt32(reader["RRP"]);
                        result.ProductPackage = Convert.ToSingle(reader["Package"]);
                        result.Total = Convert.ToSingle(reader["Total"]);
                        result.Objective = Convert.ToSingle(reader["Objective"]);
                        result.RawMat = Convert.ToSingle(reader["RawMat"]);
                        result.Biogro = Convert.ToDouble(reader["Biogro"]);
					}
				}
				catch (Exception e)
				{
                    throw new Exception(e.Message);
				}
				finally
				{
					//reader.Close();
					connection.Close();
				}
				return result;
			}
			internal float getPackagePriceById(string id) 
			{
				OleDbCommand cmd;
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				OleDbDataReader reader;
				float PackagePrice = 0;
				try
				{
					connection.Open();
					cmd = new OleDbCommand("Select * from Packaging where Package='" + id + "'", connection);
					reader = cmd.ExecuteReader();
					if (reader.Read())
					{
						PackagePrice = Convert.ToSingle(reader["Cost"]);
					}
				}
				catch (Exception e)
				{
                    throw new Exception(e.Message);
				}
				finally
				{
					//reader.Close();
					connection.Close();
				}
				return PackagePrice;
			}
            internal Packaging getPackageById(string id) 
            {
                OleDbCommand cmd;
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                OleDbDataReader reader;
                Packaging result = null;
                try
                {
                    connection.Open();
                    cmd = new OleDbCommand("Select * from Packaging where Package='" + id + "'", connection);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        result = new Packaging();
                        result.Package = Convert.ToInt32(reader["Package"]);
                        result.Price = Convert.ToSingle(reader["Cost"]);
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally 
                {
                    //reader.Close();
                    connection.Close();
                }
                return result;
            }
            internal CUR getCURbyCurrency(string Currency) 
            {
                OleDbCommand cmd;
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                OleDbDataReader reader;
                CUR result = null;
                try
                {
                    connection.Open();
                    cmd = new OleDbCommand("Select * from [CUR EXCH] where Currency_Code='" + Currency + "'", connection);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        result = new CUR();
                        result.CurrencyName = Convert.ToString(reader["Currency_Code"]);
                        result.NewZealandDollar = Convert.ToSingle(reader["NZD"]);
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    //reader.Close();
                    connection.Close();
                }
                return result;
            }
            internal Packaging getPackage()
            {
                _Packaging = new Packaging();
                string sql = "Select * from Package";
                DBHelper.GetDBConnection.Open();
                try
                {
                    OleDbDataReader Reader = DBHelper.GetDataReader(sql);
                    while (Reader.Read()) { }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
                return _Packaging;
            }
            internal Supplier_RM getSupplierRM()
            {
                _Supplier = new Supplier_RM();
                string sql = "select * from Supplier_RM";
                DBHelper.GetDBConnection.Open();
                try
                {
                    OleDbDataReader Reader = DBHelper.GetDataReader(sql);
                    while (Reader.Read()) { }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
                return _Supplier;

            }
			internal Supplier_RM getSupplierRMbyCodeAndSupplier(string code, string suppliername) 
			{
				OleDbCommand cmd;
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				OleDbDataReader reader;
				Supplier_RM result = null;
				try
				{
					connection.Open();
					cmd = new OleDbCommand("Select * from [Supplier RM] where Supplier_Name='" + suppliername + "' And Code='" + code
						+"'", connection);
					reader = cmd.ExecuteReader();
					int a = 0;
					if (reader.Read())
					{
                        result = new Supplier_RM();
						result.TradeName = Convert.ToString(reader["Trade_Name"]);
						result.KeyProperty = Convert.ToString(reader["Key_Properties"]);
						result.TotalCost = Convert.ToInt32(reader["Total_Cost"]);
					}
				}
				catch (Exception e)
				{
                    throw new Exception(e.Message);
				}
				finally
				{
					//reader.Close();
					connection.Close();
				}
				return result;
			}
			internal string[] getSupplierNameBycode(string code) 
			{
				OleDbCommand cmd;
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				OleDbDataReader reader;
				string[] SupplierName = new string[100];
				try
				{
					connection.Open();
					cmd = new OleDbCommand("Select Supplier_Name from [Supplier RM] where Code='" + code + "'", connection);
					reader = cmd.ExecuteReader();
					int a = 0;
					if (reader.Read())
					{
						SupplierName[a] = Convert.ToString(reader["Supplier_Name"]);
						a++;
					}
				}
				catch (Exception e)
				{
                    throw new Exception(e.Message);
				}
				finally
				{
					//reader.Close();
					connection.Close();
				}
				return SupplierName;
			}
            internal string getSupplierCode(string code) 
            {
                OleDbCommand cmd;
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                OleDbDataReader reader;
                string result = null;
                try
                {
                    connection.Open();
                    cmd = new OleDbCommand("Select Code from [Supplier RM] where Supplier_Code='" + code + "'", connection);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        result = Convert.ToString(reader["Code"]);

                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    //reader.Close();
                    connection.Close();
                }
                return result;
            }
			internal Supplier_RM getSupplierByCode(string Code) 
			{
				OleDbCommand cmd;
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				OleDbDataReader reader;
				Supplier_RM result = null;
				try
				{
					connection.Open();
					cmd = new OleDbCommand("Select * from [Supplier RM] where Code='" + Code + "'", connection);
					reader = cmd.ExecuteReader();
					if (reader.Read())
					{
						result = new Supplier_RM();
                        result.Code = Convert.ToString(reader["Code"]);
                        result.Category = Convert.ToString(reader["Category"]);
                        result.TradeName = Convert.ToString(reader["Trade_Name"]);
                        result.INCIName = Convert.ToString(reader["INCI_Name"]);
                        result.KeyProperty = Convert.ToString(reader["Key_Properties"]);
                        result.SupplierCode = Convert.ToString(reader["Supplier_Code"]);
                        result.SupplierName = Convert.ToString(reader["Supplier_Name"]);
                        result.SupplierCountry = Convert.ToString(reader["Supplier_Country"]);
                        result.CurrencyName = Convert.ToString(reader["Currency_Name"]);
                        result.LocalPrice = Convert.ToSingle(reader["Local_Price"]);
                        result.EstimateCost = Convert.ToSingle(reader["Estimate_Cost"]);
                        result.FreightType = Convert.ToString(reader["Freight_Type"]);
                        result.EstimatedLoss = Convert.ToSingle(reader["Estimate_Loss"]);
                        result.EstimatePrice = Convert.ToSingle(reader["Estimate_Price"]);
                        result.TotalCost = Convert.ToSingle(reader["Total_Cost"]);
                        result.Packsize = Convert.ToSingle(reader["Pack_Size"]);
                        result.EstimateDateFrom = Convert.ToString(reader["Estimate_DateFrom"]);
                        result.EstimateDateTo = Convert.ToString(reader["Estimate_DateTo"]);
                        result.TotalCostofShipment = Convert.ToSingle(reader["Total_Cost_of_Shipment"]);
						

					}
				}
				catch (Exception e)
				{
                    throw new Exception(e.Message);
				}
				finally
				{
					//reader.Close();
					connection.Close();
				}
				return result;
			}
            internal RawMaterialSupplier getRawMaterialSupplierByCode(string Code)
            {
                OleDbCommand cmd;
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                OleDbDataReader reader;
                RawMaterialSupplier result = null;
                try
                {
                    connection.Open();
                    cmd = new OleDbCommand("Select * from [Raw Mat Suppliers] where Supplier_Code='" + Code + "'", connection);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        result = new RawMaterialSupplier();
                        result.Code = (string)reader["Supplier_Code"];
                        result.Name = (string)reader["Supplier_Name"];
                        result.Address = (string)reader["Address"];
                        result.Phone = (string)reader["Phone"];
                        result.Email = (string)reader["Email"];
                        result.Contact = (string)reader["Contact"];
                        result.Category = (string)reader["Category"];
                        result.Notes = (string)reader["Notes"];

                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    //reader.Close();
                    connection.Close();
                }
                return result;
            }
            internal RawMaterialSupplier getRawMaterialSupplier()
            {
                _RawMaterialSupplier = new RawMaterialSupplier();
                string sql = "select * from RawMaterialSupplier";
                try
                {
                    DBHelper.GetDBConnection.Open();
                    OleDbDataReader Reader = DBHelper.GetDataReader(sql);
                    while (Reader.Read()) { }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
                return _RawMaterialSupplier;
            }
            internal Freight getFreight()
            {
                _Freight = new Freight();
                
                //string sql = "select * from Freight";
                try
                {
                    //DBHelper.GetDBConnection.Open();
                    SqlCommand cmd = new SqlCommand("Select * from Freight");
                    SqlDataReader myReader = cmd.ExecuteReader();
                    
                    while (myReader.Read())
                    {
                        _Freight.FreightType = (myReader.GetString(1));
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
                return _Freight;
            }
            internal Freight getFreightByType(string Type) 
            {
                OleDbCommand cmd;
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                OleDbDataReader reader;
                Freight result = null;
                try
                {
                    connection.Open();
                    cmd = new OleDbCommand("Select * from Freight where Freight_Type='" + Type + "'", connection);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        result = new Freight();
                        result.FreightType = (string)reader["Freight_Type"];
                        result.Estimated = Convert.ToSingle(reader["Estimated_Cost"]);
                        result.ProductWeight = Convert.ToSingle(reader["Product_Weight"]);
                        result.PackagingWeight = Convert.ToSingle(reader["Packaging_Weight"]);
                        result.Total = Convert.ToSingle(reader["Total_Weight"]);
                        result.SupplierPrice = Convert.ToInt32(reader["Supplier_Price"]);
                        result.VariableFuel = Convert.ToSingle(reader["Variable_FuelComponent"]);
                        result.Supplier = Convert.ToString(reader["Supplier"]);
                        result.Origin = Convert.ToString(reader["Origin"]);
                        result.RateType = Convert.ToString(reader["Rate_Type"]); ;
                        

                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    //reader.Close();
                    connection.Close();
                }
                return result;
            }
            internal CUR getCUR()
            {
                //_CUR = new CUR();
                string sql = "Select * from CUR";
                try
                {
                    DBHelper.GetDBConnection.Open();
                    OleDbDataReader Reader = DBHelper.GetDataReader(sql);
                    while (Reader.Read()) { }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
                return _CUR;
            }
            internal void addRawMaterialSupplier(RawMaterialSupplier _RawMaterialSupplier) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionStringExclusive);
                            
                try
                {

                    string query = "INSERT INTO [Raw Mat Suppliers] VALUES('" + _RawMaterialSupplier.Code + "','" + _RawMaterialSupplier.Name + "','" + _RawMaterialSupplier.Address + "','" + _RawMaterialSupplier.Phone + "','" + _RawMaterialSupplier.Email + "','" + _RawMaterialSupplier.Contact + "','" + _RawMaterialSupplier.Category + "', '" + _RawMaterialSupplier.Notes + "')";
                    
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally 
                {
                    connection.Close();
                }
            }
            internal void deleteRawMaterialSupplier(RawMaterialSupplier _RawMaterialSupplier) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "DELETE FROM [Raw Mat Suppliers] Where Supplier_Code='" + _RawMaterialSupplier.Code + "'";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
            internal void deleteIngredientByIngredient(Ingredient _Ingredient) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "DELETE FROM Ingredient Where Code='" + _Ingredient.IngredientCode + "'";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void deleteIngredient(string code) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "DELETE FROM Ingredient Where [Supplier Code]='" + code + "'";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void setRawMaterialSupplier(RawMaterialSupplier _RawMaterialSupplier) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionStringExclusive);

                try
                {

                    string query = "UPDATE [Raw Mat Suppliers],Ingredient "+ 
                    "SET [Raw Mat Suppliers].Supplier_Name='"+_RawMaterialSupplier.Name+"'"+
                    ",Address='"+_RawMaterialSupplier.Address+"',Phone='"+_RawMaterialSupplier.Phone+"',Email='"+_RawMaterialSupplier.Email+"'"+
                    ",Contact='" + _RawMaterialSupplier.Contact + "',[Raw Mat Suppliers].Category='" +_RawMaterialSupplier.Category+"',Notes='" + _RawMaterialSupplier.Notes + "'" +
                    "Where [Raw Mat Suppliers].Supplier_Code='" + _RawMaterialSupplier.Code + "'";




                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void setSupplierRM(Supplier_RM Supplier_RM)
			{
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionStringExclusive);

				try
				{

					string query = "UPDATE [Supplier RM] SET Supplier_Code='" + Supplier_RM.Code + "',Sup";

					OleDbCommand cmd = new OleDbCommand(query, connection);
					connection.Open();
					cmd.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
				finally
				{
					connection.Close();
				}

            }
            internal void addSupplierRM(Supplier_RM Supplier_RM) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "INSERT INTO [Supplier RM]VALUES('"+Supplier_RM.Code+"','"+Supplier_RM.Category+"','"+Supplier_RM.TradeName+"',"+
                        "'"+Supplier_RM.INCIName+"','"+Supplier_RM.KeyProperty+"','"+Supplier_RM.SupplierCode+"','"+Supplier_RM.SupplierName+"',"+
                        "'"+Supplier_RM.SupplierCountry+"','"+Supplier_RM.CurrencyName+"','"+Supplier_RM.LocalPrice+"','"+Supplier_RM.EstimateCost+"',"+
                        "'"+Supplier_RM.FreightType+"','"+Supplier_RM.EstimatedLoss+"','"+Supplier_RM.EstimatePrice+"','"+Supplier_RM.TotalCost+"',"+
                        "'"+Supplier_RM.Packsize+"','"+Supplier_RM.EstimateDateFrom+"','"+Supplier_RM.EstimateDateTo+"','"+Supplier_RM.TotalCostofShipment+"')";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void deleteSupplierRM(Supplier_RM Supplier_RM) 
            {
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				try
				{
					string query = "DELETE FROM [Supplier RM] Where Code='" + Supplier_RM.Code + "'";
					OleDbCommand cmd = new OleDbCommand(query, connection);
					connection.Open();
					cmd.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
				finally
				{
					connection.Close();
				}
            }
            internal void setPackage(Packaging _Packaging) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "UPDATE Packaging SET Package='"+_Packaging.Package+"',Cost='"+_Packaging.Price+"' where Package='"+_Packaging.Package+"'";


                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void addPackege(Packaging _Packaging) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "INSERT INTO [Packaging](Package,Cost) VALUES('" + _Packaging.Package+ "','" + _Packaging.Price + "')";
                    
                    
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally 
                {
                    connection.Close();
                }
                
            }
            internal void deletePackage(Packaging _Packaging) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "DELETE FROM Packaging Where Package='"+_Packaging.Package+"'";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void setSTD(STD _STD) 
            {
                try
                {
                    string query = "";
                    OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionStringExclusive);
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();

                    OleDbDataReader myReader = cmd.ExecuteReader();

                    while (myReader.Read())
                    {
                        _Freight.FreightType = (myReader.GetString(1));
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
            }
            internal void addSTD(STD _STD) 
            {
                try
                {
                    string query = "INSERT INTO [STD QTY] VALUES('" + _STD.ProductId + "','" + _STD.ProductName + "','" + _STD.ProductIngredient + "'," +
                        "'" + _STD.ProductUnit + "','" + _STD.Description + "','" + _STD.Comments + "','" + _STD.Date + "','" + _STD.Loss + "','" + _STD.BatchSize + "'," +
                        "'" + _STD.RRP + "','" + _STD.ProductPackage + "','" + _STD.Total + "','" + _STD.Objective + "','" + _STD.RawMat + "','" + _STD.Biogro + "')";
                    OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionStringExclusive);
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();

                    OleDbDataReader myReader = cmd.ExecuteReader();

                    while (myReader.Read())
                    {
                        _Freight.FreightType = (myReader.GetString(1));
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
            }
            internal void deleteSTD(STD _STD) 
            {
				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				try
				{
					string query = "DELETE FROM [STD QTY] Where Product_Id='" + _STD.ProductId + "'";
					OleDbCommand cmd = new OleDbCommand(query, connection);
					connection.Open();
					cmd.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
				finally
				{
					connection.Close();
				}
            }
            internal void addFreight(Freight _Freight)
            {
                try
                {
                    string query = "INSERT INTO Freight VALUES('"+_Freight.FreightType+"','"+_Freight.Estimated+"','"+_Freight.Supplier+"','"+_Freight.Origin+"','"+_Freight.RateType+"','"+_Freight.ProductWeight+"','"+_Freight.PackagingWeight+"','"+_Freight.Total+"','"+_Freight.SupplierPrice+"','"+_Freight.VariableFuel+"')";
                    OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    DBHelper.GetDBConnection.Close();
                }
            }
            internal void setFreight(Freight _Freight)
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
					string query = "UPDATE Freight SET Estimated_Cost='" + _Freight.Estimated + "'," +
                    "Supplier='" + _Freight.Supplier + "',Origin='" + _Freight.Origin + "',Rate_Type='" + _Freight.RateType + "'," +
                    "Product_Weight='" + _Freight.ProductWeight + "',Packaging_Weight='" + _Freight.PackagingWeight + "'," +
                    "Total_Weight='" + _Freight.Total + "',Supplier_Price='" + _Freight.SupplierPrice + "',Variable_FuelComponent='" + _Freight.VariableFuel + "'" +
                    "where Freight_Type='" + _Freight.FreightType + "'";


                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void deleteFreight(Freight _Freight) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "DELETE FROM Freight Where Freight_Type='" + _Freight.FreightType + "'";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void setCUR(CUR _CUR) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "UPDATE [CUR EXCH] SET NZD='" + _CUR.NewZealandDollar + "' where Currency_Code='"+_CUR.CurrencyName+"'";


                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void addCUR(CUR _CUR) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "INSERT INTO [CUR EXCH] VALUES('" + _CUR.CurrencyName + "','"+_CUR.NewZealandDollar+"')"; 
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            internal void deleteCUR(string _CUR) 
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "DELETE FROM [CUR EXCH] Where Currency_Code='" + _CUR + "'";
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
			internal void deleteFormular(Formula _Formula, List<string> t) 
			{
			}
			internal void setFormula(Formula _Formula, List<string> t) 
			{
			}
			internal void addFormula(Formula _Formula, List<string> t) 
			{

				OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
				try
				{
					foreach(string index in t)
					{
					string query = "INSERT INTO Ingredient VALUES('"+_Formula.ProductId+"','"+index+"')";

					OleDbCommand cmd = new OleDbCommand(query, connection);
					connection.Open();
					cmd.ExecuteNonQuery();
					}
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
				finally
				{
					connection.Close();
				}
			}
            internal void addIngredient(Ingredient _Ingredient)
            {
                OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
                try
                {
                    string query = "INSERT INTO Ingredient VALUES('"+_Ingredient.IngredientCode+ "','"+_Ingredient.SupplierCode+"','"+_Ingredient.SupplierName+"','" +_Ingredient.INCIName + "','"+_Ingredient.TradeName+"','" + _Ingredient.KeyProperties + "','" + _Ingredient.LocalPrice+ "','"+_Ingredient.Category+"')";
                    
                    OleDbCommand cmd = new OleDbCommand(query, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
        #region DataTable
        internal DataTable GetDataTable(OleDbCommand OleDB)
        {
            OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
            DataTable DT=new DataTable();
            try
            {
                connection.Open();
                OleDB.Connection = connection;
                OleDbDataAdapter DA = new OleDbDataAdapter(OleDB);

                DA.Fill(DT);
                if (DT.Rows.Count > 0) 
                {
                    
                }
                
            }
            catch(Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                connection.Close();
            }
            return DT;
            
        }
        internal Ingredient getIngredientByCode(string Code) 
        {
            OleDbCommand cmd;
            OleDbConnection connection = new OleDbConnection(DBHelper.ConnectionString);
            OleDbDataReader reader;
            Ingredient result = null;
            try
            {
                connection.Open();
                cmd = new OleDbCommand("Select * from Ingredient where Code='" + Code + "'", connection);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = new Ingredient();
                    result.IngredientCode = Convert.ToString(reader["Code"]);
                    result.SupplierCode = Convert.ToString(reader["Supplier Code"]);
                    result.SupplierName = Convert.ToString(reader["Supplier_Name"]);
                    result.INCIName = Convert.ToString(reader["INCINAME"]);
                    result.TradeName = Convert.ToString(reader["TradeName"]);
                    result.KeyProperties = Convert.ToString(reader["KeyProperty"]);
                    result.LocalPrice = Convert.ToSingle(reader["LocalPrice"]);
                    result.Category = Convert.ToString(reader["Category"]);



                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                //reader.Close();
                connection.Close();
            }
            return result;
        }
        #endregion


        internal Supplier_RM getSupplierBySupplierCode(string r)
        {
            throw new NotImplementedException();
        }
    }

    
}
